﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Drawing;
using System.Data;
using System.Runtime.InteropServices;

namespace POC.Reports.Document
{
   public static  class ExcelInterOpExtn
    {
       public static void ToXLS(this DataTable dataTable, string filePath)
       {
           Excel.Application application = new Excel.Application();
           bool fileExists = false;
           try
           {
               FileInfo file = new FileInfo(filePath);
               fileExists = file.Exists;
               Excel.Workbook workbook = (fileExists) ? application.Workbooks.Open(filePath) 
                   : application.Workbooks.Add();
               Excel.Worksheet worksheet = workbook.Sheets[1];            

               // Lets find some rows 
              
               int row = worksheet.Rows.CurrentRegion.EntireRow.Count;
               int col = worksheet.Columns.CurrentRegion.EntireColumn.Count;


               var columns = dataTable.Columns.Count;
               var rows = dataTable.Rows.Count;

               // No Header
               if (row <= 1)
               {
                   Excel.Range header = worksheet.Range["A1", String.Format("{0}{1}", GetExcelColumnName(columns), 1)];

                   object[,] hdata = new object[1, columns];
                   for (int columnNumber = 0; columnNumber < columns; columnNumber++)
                   {
                       hdata[0, columnNumber] = dataTable.Columns[columnNumber].ColumnName;
                   }
                   header.Value = hdata;
               }
               string startRange = "";
               string endRange = "";
               if (row <= 1)
               {
                   startRange = "A2";
                   endRange = String.Format("{0}{1}", GetExcelColumnName(columns), rows +1);
               }
               else
               {
                   startRange = String.Format("{0}{1}", "A", row + 1);
                   endRange = String.Format("{0}{1}", GetExcelColumnName(columns), row + rows);
               }
               // Lets write the Header....
               Excel.Range range = worksheet.Range[startRange, endRange];

               object[,] data = new object[rows, columns];

               for (int rowNumber = 0; rowNumber < rows; rowNumber++)
               {
                   for (int columnNumber = 0; columnNumber < columns; columnNumber++)
                   {
                       data[rowNumber, columnNumber] = dataTable.Rows[rowNumber][columnNumber].ToString();
                   }
               }

               range.Value = data;
               if (fileExists)
               {
                   workbook.Save();
               }
               else
               {
                   workbook.SaveAs(filePath);
               }
               workbook.Close();
               
           }
           catch (Exception comEx)
           {
           }
           finally
           {
               if (application != null)
               {
                   application.Quit();
                   Marshal.ReleaseComObject(application);
               }
           }
       }
       private static string GetExcelColumnName(int columnNumber)
       {
           int dividend = columnNumber;
           string columnName = String.Empty;
           int modulo;

           while (dividend > 0)
           {
               modulo = (dividend - 1) % 26;
               columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
               dividend = (int)((dividend - modulo) / 26);
           }

           return columnName;
       }
    }
}
